﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'RegistroOTDetalleRep')
	BEGIN
		DROP  Procedure  RegistroOTDetalleRep
	END
GO 

CREATE PROCEDURE [dbo].[RegistroOTDetalleRep]
	(	
		@EmpresaId	AS INT,
		@NumeroOT	AS VARCHAR(30)
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @id AS INT
	DECLARE @OTDetalle Table
		(NumeroOT		VARCHAR(30),
		 PuestoNombre	VARCHAR(50),
		 Horas			DECIMAL(10,2),
		 Total			MONEY)


	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, 'Catalog'			AS TableName	UNION
	SELECT 1 AS TableId, 'OrdenTrabajo'		AS TableName	UNION
	SELECT 2 AS TableId, 'OTDetalle'		AS TableName	UNION
	SELECT 3 AS TableId, 'OTMaterial'		AS TableName    UNION
	SELECT 4 AS TableId, 'OTGastoDirecto'	AS TableName    UNION
	SELECT 5 AS TableId, 'OTGastoIndirecto'		AS TableName    UNION
	SELECT 6 AS TableId, 'OTMaquina'		AS TableName   -- UNION		

	SELECT @id = id 
	FROM Lit_OrdenTrabajo 
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT

	-- OrdenTrabajo
	SELECT      o.NumeroOT,
			  e.Nombre AS EmpresaNombre,
			  FechaInicial,
			  FechaFinal,
			  CASE o.Estado
					WHEN 'A' THEN 'EN PROCESO'
					WHEN 'T' THEN 'FINALIZADA'
			  END AS Estado,
			  o.Unidades As Cantidad,
			  CONVERT(DECIMAL(10,2), 0) AS CantidadProducida
	FROM lit_ordenTrabajo o     
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id 
	WHERE o.Id = @id

	-- OT Detalle (Mano de obra)
	INSERT INTO @OTDetalle
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.operarioId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT, p.Nombre
		UNION
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoAyudantexHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT, p.Nombre
	
	SELECT NumeroOT, PuestoNombre, SUM(Horas) AS Horas, SUM(Total) AS Total
	FROM @OTDetalle
	GROUP BY NumeroOT, PuestoNombre	
	
	-- OT Material
	SELECT ot.NumeroOT, m.ArticuloId, art_nombre AS ArticuloNombre, Cantidad, CostoArticuloUnitario AS Costo, SUM(CostoArticuloTotal) AS Total
	FROM Lit_OTMaterial m
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = m.OTId
	INNER JOIN Articulo a ON m.ArticuloId = a.Art_id
	WHERE OTId = @id
	AND a.Emp_Id = @empresaId
	GROUP BY ot.NumeroOT, m.ArticuloId, art_nombre, Cantidad, CostoArticuloUnitario

	-- OT Gastos Directos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
	WHERE OTId = @id AND g.Tipo = 'D'

	-- OT Gastos Indirectos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
	WHERE OTId = @id AND g.Tipo = 'I'
	UNION
	SELECT NumeroOT, 0 As GastoId, 'Mano de Obra Indirecta' As GastoNombre, ISNULL(ManoObraIndirecta,0)
	FROM Lit_OrdenTrabajo
	WHERE Id = @id
	AND ManoObraIndirecta IS NOT NULL
	AND ManoObraIndirecta > 0

	-- OT Maquina
	SELECT ot.NumeroOT, o.MaquinaId, m.Nombre AS MaquinaNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, o.CostoMaquinaxHora, SUM(CostoMaquina) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
	INNER JOIN Lit_Maquina m ON o.MaquinaId = m.Id
	WHERE OTId = @id	  
	GROUP BY ot.NumeroOT, o.MaquinaId, m.Nombre, o.CostoMaquinaxHora
           	            
	SET NOCOUNT OFF
END
GO